package com.kcs.fxmargin.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import com.kcs.common.utils.OIDGenerator;
import com.kcs.fxmargin.bo.FXEndOfDayBO;
import com.kcs.fxmargin.model.MCustInfo;
import com.kcs.fxmargin.model.TDeals;

public class FXEndOfDayDAO {
	public FXEndOfDayBO getMCustInfoList(FXEndOfDayBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		MCustInfo cust = null;
		List<MCustInfo> mCustInfoList = null;
		
		try{
			
			sql = "select id, cif_no, margin_acc_no "+
				  " from m_cust_info "+
				  "	where active_status='A'";
			
			System.out.println(sql);
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if(rs != null){
				mCustInfoList = new ArrayList<MCustInfo>();
				
				while(rs.next()){
					cust = new MCustInfo();
					cust.setId(rs.getString("id"));
					cust.setCifNo(rs.getString("cif_no"));
					cust.setMarginAccNo(rs.getString("margin_acc_no"));
					
					/*cust.setCustName(rs.getString("cust_name"));
					cust.setMarginAccName(rs.getString("margin_acc_name"));
					cust.setDepositAccNo(rs.getString("deposit_acc_no"));
					cust.setDepositAccName(rs.getString("deposit_acc_name"));
					cust.setCustLimitAmt(rs.getBigDecimal("cust_limit_amt"));
					cust.setCustStatusCode(rs.getString("cust_status_code"));
					cust.setActiveStatus(rs.getString("active_status"));
					cust.setCreateby(rs.getString("create_by"));
					cust.setCreateDate(rs.getDate("create_dt"));
					cust.setUpdateBy(rs.getString("update_by"));
					cust.setUpdateDate(rs.getDate("update_dt"));
					cust.setRowVersion(rs.getInt("row_version"));*/
					mCustInfoList.add(cust);
				}
			}
			
			bo.setmCustInfoList(mCustInfoList);
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public FXEndOfDayBO getTDealsList(FXEndOfDayBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		TDeals td = null;
		List<TDeals> tDealsList = null;
		
		try{
			
			sql = "select * "+
				  " from t_deals "+
				  " where active_status='A' "+
				  " and cif_no = ? ";
			
			System.out.println(sql);
			System.out.println("cif_no: "+bo.getCifNo());
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bo.getCifNo());
			rs = pstmt.executeQuery();
			
			if(rs != null){
				tDealsList = new ArrayList<TDeals>();
				
				while(rs.next()){
					td = new TDeals();
					td.setId(rs.getString("id"));
					td.setCifNo(rs.getString("cif_no"));
					td.setDealId(rs.getString("deal_id"));
					td.setParentId(rs.getString("parent_id"));
					td.setTransType(rs.getString("trans_type"));
					td.setBuySell(rs.getString("buy_sell"));
					td.setTransDate(rs.getDate("trans_date"));
					td.setValueDate(rs.getDate("value_date"));
					td.setCcy1(rs.getString("ccy1"));
					td.setDealAmt1(rs.getBigDecimal("deal_amt1"));
					td.setExchRate(rs.getBigDecimal("exch_rate"));
					td.setCcy2(rs.getString("ccy2"));
					td.setEquityAmt(rs.getBigDecimal("equity_amt"));
					td.setOutstdAmt(rs.getBigDecimal("outstd_amt"));
					td.setDocType(rs.getString("doc_type"));
					td.setImRequired(rs.getBigDecimal("im_required"));
					td.setMtmRate(rs.getBigDecimal("mtm_rate"));
					td.setUnrealizedPl(rs.getBigDecimal("unrealized_pl"));
					td.setClosedRate(rs.getBigDecimal("closed_rate"));
					td.setRealizedPl(rs.getBigDecimal("realized_pl"));
					td.setDealStatus(rs.getString("deal_status"));
					td.setActiveStatus(rs.getString("active_status"));
					td.setCreateby(rs.getString("create_by"));
					td.setCreateDate(rs.getDate("create_dt"));
					td.setUpdateBy(rs.getString("update_by"));
					td.setUpdateDate(rs.getDate("update_dt"));
					td.setRowVersion(rs.getInt("row_version"));
					tDealsList.add(td);
				}
			}
			
			bo.settDealsList(tDealsList);
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public boolean insertTDealsHistory(String tDealsId, String asOfDate, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		boolean result =  false;
		String id = null;

		try{
			
			sql = "insert into t_deals_history "+
				  " (id "+
				  " , cif_no, deal_id, parent_id, trans_type, buy_sell, trans_date, value_date, ccy1, deal_amt1, exch_rate "+
				  " , ccy2, equity_amt, outstd_amt, doc_type, im_required, mtm_rate, unrealized_pl, closed_rate, realized_pl "+
				  " , deal_status, as_of_date, instrument, branch, comments, pre_settle_risk, pre_settle_used, active_status, create_by, create_dt, update_by, update_dt, row_version, tenor) "+
				  " select "+
				  " ? "+
				  " , cif_no, deal_id, parent_id, trans_type, buy_sell, trans_date, value_date, ccy1, deal_amt1, exch_rate "+
				  " , ccy2, equity_amt, outstd_amt, doc_type, im_required, mtm_rate, unrealized_pl, closed_rate, realized_pl "+
				  " , deal_status, ? "+
				  " , instrument, branch, comments, pre_settle_risk, pre_settle_used, 'A', create_by, create_dt, '', null, 1, tenor "+
				  " from t_deals  "+
				  " where id = ?";
			
			id = OIDGenerator.create();
			
			System.out.println(sql);
			System.out.println("id: "+id);
			System.out.println("asOfDate: "+asOfDate);
			System.out.println("t_deals_id: "+tDealsId);
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, asOfDate);
			pstmt.setString(3, tDealsId);
			pstmt.executeUpdate();
			
			result = true;
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return result;
	}
	
	public boolean deleteTDeals(String asOfDate, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		boolean result =  false;

		try{
			
			sql = "delete from t_deals where value_date < ?";

			System.out.println(sql);
			System.out.println("asOfDate: "+asOfDate);
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, asOfDate);
			pstmt.executeUpdate();
			
			result = true;
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return result;
	}
	
	public FXEndOfDayBO getOnlineFlag(FXEndOfDayBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;

		try{
			
			sql = "SELECT VALUE FROM c_config_sys WHERE keys='ONLINE_FLAG'";

			System.out.println(sql);
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if(rs != null){
				if(rs.next()){
					bo.setOnlineFlag(rs.getString(1));
				}
			}

		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public FXEndOfDayBO callMarginLimit(FXEndOfDayBO bo, Connection conn)throws Exception{
		CallableStatement cstmt = null;
		ResultSet rs = null;
		String sql = null;
		
		try{
			sql = "{call SPCalMarginLimit(?,?,?)}";
			
			System.out.println(sql);
			System.out.println("> cifNo: "+bo.getCifNo());
			System.out.println("> asOfDate: "+bo.getAsOfDate());
		
			cstmt = conn.prepareCall(sql);
			cstmt.setString(1, bo.getCifNo());
			cstmt.setString(2, bo.getAsOfDate());
			cstmt.registerOutParameter(3, Types.NUMERIC);
			cstmt.execute();
			
			bo.setMarginLimit((cstmt.getObject(3)!=null)?cstmt.getObject(3).toString():"0");
			
		}catch(Exception e){
			throw e;
		}finally{
			if(cstmt != null){
				try{
					cstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public FXEndOfDayBO callArchiveData(FXEndOfDayBO bo, Connection conn)throws Exception{
		CallableStatement cstmt = null;
		ResultSet rs = null;
		String sql = null;
		
		try{
			sql = "{call SP_ArchiveData(?,?)}";
			
				System.out.println(sql);
				System.out.println("> startDate: "+bo.getStartDate());
				System.out.println("> endDate: "+bo.getEndDate());
			
				cstmt = conn.prepareCall(sql);
				cstmt.setDate(1, bo.getStartDate());
				cstmt.setDate(2, bo.getEndDate());
				cstmt.execute();
			 
		}catch(Exception e){
			throw e;
		}finally{
			if(cstmt != null){
				try{
					cstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public boolean deleteTDealsHistory(String asOfDate, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		boolean result =  false;

		try{
			
			sql = "delete from t_deals_history where as_of_date = ?";

			System.out.println(sql);
			System.out.println("asOfDate: "+asOfDate);
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, asOfDate);
			pstmt.executeUpdate();
			
			result = true;
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return result;
	}
}
